When using DXL to output to an Excel file, because object text contains multiple paragraphs and bullet points, the outputted object text does not stay in a single cell. Without modifying the actual text in the module, how can I force the outputted text in a single cell? Many thanks.
|
Re: output paragraph and bullet point --- Martin |
Re: output paragraph and bullet point Martin_Hunter - Thu Apr 07 11:26:43 EDT 2011
well now any quote in your text will ruin your export ;-) You guys need to take a look at the CSV specification. In CSV quotes must be put around any string containing commas, but additionally you need to escape your quotes (using two quotes) when surrounding your string with quotes.
Regexp gListviewEscapeCharacters = regexp "[\"\\n\\t]"
// This function will escape newline (\n) tab (\t) and hyphens (")
// to export them to a tab separated string
// i.e. Hello\n"you"\t will become "Hello\n""You""\t"
// This can be pasted to Excel.
void AddEscapedStringToBuffer (string s, Buffer b) {
// Buffer bResult = create()
int i
bool needEscape = false
if (null s) return
// check if we need to escape the string
if (gListviewEscapeCharacters s) needEscape = true
if ( needEscape ) {
b += "\""
for i in 0:length(s)-1 do {
if ( s[i] == '\"') b += '\"'
b += s[i]
}
b += "\""
} else {
// if not: just add it unescaped ...
b += s
return
}
}
string escapeTabsAndNewlines (string s) {
Buffer v = create()
AddEscapedStringToBuffer (s, v)
string result = stringOf v
delete v
return result
}
Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS
|
Re: output paragraph and bullet point <0> RAW text, no OLE nor bolding or color or anything. see string = plainText(string) or use string = obj."Object Text". <1> Replace its EOLs and TABs presumably with double-spaces. There are 3 EOLs: "\n\r\v". For me, replacing with "-EOL-" or "-TAB-" looked horrible. <2> each natural double quote (in the text) needs to be turned into two double-quotes. <3a> the first cell of an export row must be prefixed with a double-quote: buf += "\"" <3b> each additional cell of the row must be prefixed with a comma and double-quote: buf += ",\"" <4> the cell must be suffixed with a double-quote: buf += "\"" Since the above approach surrounds all cells with double-quotes separated by commas, you won't need to convert nor escape natural commas. An export "row" is composed of a first cell and additional cells as above, end of row has an EOL "\n". Yuuuck.
|
Re: output paragraph and bullet point llandale - Thu Apr 07 11:45:52 EDT 2011
|
Re: output paragraph and bullet point denny.chiang - Thu Apr 07 15:01:57 EDT 2011 I can cook an egg but have no idea how they manufacture them; nor do I care. If its raw text then building a CSV file and then invoking Excel to open that file is quite reasonable. The alternative is to invoke Excel, get an OLE handle on it, and "export" your stuff directly into the application, one copy-paste-advance-next-cell operation at a time. You could then issue a 'save' command and Viola! You have created your XLS file. Other folks will need to help with that; I'm a CSV dude.
|
Re: output paragraph and bullet point llandale - Thu Apr 07 16:12:40 EDT 2011
Paul Miller Melbourne, Australia |
Re: output paragraph and bullet point |
Re: output paragraph and bullet point Hi all, You can use the escape() function in DXL as well. Object o = current Object string sText = o."Object Text""" sText = escape(sText, '\"', "\"") When you go to output the code, wrap it with escaped quotes: output << "\"" sText "\"" "," "\"" sText "\"" "," "\"" sText "\"" "\n" Obviously this is for CSV export which won't do any richText, figures, tables, etc. But it's fairly simple if you just want plain text. There's also no harm in wrapping the text in quotes when there are no natural quotes in the string. Regards, Christopher |